Excel BI - Excel Challenge 770

🔰 Find the first 1000 numbers starting with double digits where number is perfectly divisible by both sum of digits and product of digits.

Illustration for Excel BI - Excel Challenge 770

Challenge Description

🔰 Find the first 1000 numbers starting with double digits where number is perfectly divisible by both sum of digits and product of digits.

Solutions

R Solution

library(tidyverse)
library(readxl)
library(duckdb)

path = "Excel/700-799/770/770 Divisible by both Sum and Product of Digits.xlsx"
test  = read_excel(path, range = "A1:A1001") %>% pull()

con = dbConnect(duckdb::duckdb())

# Step 1: Create numbers 10 to 15,000,000
dbExecute(con, "
  CREATE OR REPLACE TABLE numbers AS
  SELECT i AS number FROM range(10, 15000001) tbl(i)
")

# Step 2: Main processing query with correct list aggregation
query = "
      WITH digit_parts AS (
    SELECT 
      number,
      CAST(d.unnest AS INTEGER) AS digit
    FROM numbers,
    UNNEST(regexp_split_to_array(CAST(number AS VARCHAR), '')) AS d
  ),
  grouped_digits AS (
    SELECT
      number,
      array_agg(digit) AS digit_list
    FROM digit_parts
    GROUP BY number
  )
  SELECT
    number,
    array_length(digit_list) AS digits,
    list_sum(digit_list) AS sum_digits,
    list_product(digit_list) AS prod_digits
  FROM grouped_digits
  WHERE
    list_sum(digit_list) != 0 AND
    list_product(digit_list) != 0 AND
    number % list_sum(digit_list) = 0 AND
    number % list_product(digit_list) = 0
"

# Step 3: Run it
final_result = dbGetQuery(con, query)

result = final_result %>%
  arrange(number) %>%
  pull(number) %>%
  head(1000)

all.equal(result, test)
# > [1] TRUE

R Analysis

• Logic: Read the workbook ranges needed for the challenge.
• Strengths: The solution stays close to the text pattern itself, which makes the extraction logic easy to audit.
• Areas for Improvement: The solution assumes the workbook layout and selected ranges remain stable, so any structural change in the sheet would require small adjustments.
• Gem: The elegant part is how little code is needed once the correct intermediate representation is chosen.

Python Solution

import pandas as pd
import itertools
from math import prod

path = "700-799/770/770 Divisible by both Sum and Product of Digits.xlsx"
test = pd.read_excel(path, usecols="A", nrows=1000).iloc[:, 0].tolist()

def valid_numbers_fast(d):
    return [
        int(''.join(map(str, digits)))
        for digits in itertools.product(range(1, 10), repeat=d)
        if (s := sum(digits)) != 0
        and (p := prod(digits)) != 0
        and (n := int(''.join(map(str, digits)))) % s == 0
        and n % p == 0
    ]

result = pd.DataFrame({
    'digits': range(2, 9),
    'numbers': [valid_numbers_fast(d) for d in range(2, 9)]
})
result = result.explode('numbers').reset_index(drop=True).head(1000)

print(result['numbers'].tolist() == test) # True

Python

The Python version keeps the algorithm explicit, which helps when the challenge depends on a greedy or iterative rule.

Difficulty Level

Easy / Medium

The business rule is clear, though the workbook still needs a few transformation steps to reach the expected output.

Related Challenges

• Excel Challenge 771
• Excel Challenge 772
• Excel Challenge 773